{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "94e0736c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "588a3278",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>JPN</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>Unnamed: 7</th>\n",
       "      <th>KOR</th>\n",
       "      <th>Unnamed: 9</th>\n",
       "      <th>...</th>\n",
       "      <th>Unnamed: 20</th>\n",
       "      <th>Unnamed: 21</th>\n",
       "      <th>USA</th>\n",
       "      <th>Unnamed: 23</th>\n",
       "      <th>Unnamed: 24</th>\n",
       "      <th>Unnamed: 25</th>\n",
       "      <th>Unnamed: 26</th>\n",
       "      <th>Unnamed: 27</th>\n",
       "      <th>Unnamed: 28</th>\n",
       "      <th>Unnamed: 29</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1952</td>\n",
       "      <td>2145.00</td>\n",
       "      <td>2694.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2198</td>\n",
       "      <td>667.00</td>\n",
       "      <td>1700.00</td>\n",
       "      <td>7680.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2076.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1953</td>\n",
       "      <td>2335.00</td>\n",
       "      <td>3040.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3052</td>\n",
       "      <td>1472.00</td>\n",
       "      <td>160.00</td>\n",
       "      <td>5570.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4433.0</td>\n",
       "      <td>48.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1954</td>\n",
       "      <td>5579.00</td>\n",
       "      <td>3088.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3044</td>\n",
       "      <td>1656.00</td>\n",
       "      <td>266.00</td>\n",
       "      <td>5366.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9537.0</td>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1955</td>\n",
       "      <td>3256.00</td>\n",
       "      <td>2951.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2841</td>\n",
       "      <td>1507.00</td>\n",
       "      <td>1151.00</td>\n",
       "      <td>14016.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6173.0</td>\n",
       "      <td>93.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1956</td>\n",
       "      <td>4170.00</td>\n",
       "      <td>2672.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4060</td>\n",
       "      <td>1763.00</td>\n",
       "      <td>385.00</td>\n",
       "      <td>20979.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5727.0</td>\n",
       "      <td>388.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>2015</td>\n",
       "      <td>1241.93</td>\n",
       "      <td>648.59</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.54</td>\n",
       "      <td>412.81</td>\n",
       "      <td>430.63</td>\n",
       "      <td>3644.55</td>\n",
       "      <td>1.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>4.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.2</td>\n",
       "      <td>0.2</td>\n",
       "      <td>86.4</td>\n",
       "      <td>400.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>2016</td>\n",
       "      <td>1227.68</td>\n",
       "      <td>691.36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54.13</td>\n",
       "      <td>777.81</td>\n",
       "      <td>508.31</td>\n",
       "      <td>5095.30</td>\n",
       "      <td>3.04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>-</td>\n",
       "      <td>-</td>\n",
       "      <td>9.2</td>\n",
       "      <td>0.6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30.6</td>\n",
       "      <td>0.3</td>\n",
       "      <td>315.7</td>\n",
       "      <td>371.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>2017</td>\n",
       "      <td>2221.07</td>\n",
       "      <td>913.09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>49.37</td>\n",
       "      <td>605.47</td>\n",
       "      <td>664.84</td>\n",
       "      <td>4539.69</td>\n",
       "      <td>3.45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>+</td>\n",
       "      <td>-</td>\n",
       "      <td>1.1</td>\n",
       "      <td>1.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>18.4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>466.4</td>\n",
       "      <td>450.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>2018</td>\n",
       "      <td>644.88</td>\n",
       "      <td>699.71</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.26</td>\n",
       "      <td>370.62</td>\n",
       "      <td>431.18</td>\n",
       "      <td>4049.44</td>\n",
       "      <td>7.08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>+</td>\n",
       "      <td>-</td>\n",
       "      <td>18.3</td>\n",
       "      <td>0.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1</td>\n",
       "      <td>31.0</td>\n",
       "      <td>4.4</td>\n",
       "      <td>11.5</td>\n",
       "      <td>504.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>2019</td>\n",
       "      <td>941.18</td>\n",
       "      <td>1002.17</td>\n",
       "      <td>3.0</td>\n",
       "      <td>+</td>\n",
       "      <td>718.07</td>\n",
       "      <td>371.86</td>\n",
       "      <td>4464.47</td>\n",
       "      <td>35.75</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>+</td>\n",
       "      <td>-</td>\n",
       "      <td>9.6</td>\n",
       "      <td>1.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.5</td>\n",
       "      <td>36.1</td>\n",
       "      <td>1.3</td>\n",
       "      <td>226.1</td>\n",
       "      <td>428.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>68 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Year      JPN  Unnamed: 2  Unnamed: 3 Unnamed: 4  Unnamed: 5  Unnamed: 6  \\\n",
       "0   1952  2145.00     2694.00         NaN       2198      667.00     1700.00   \n",
       "1   1953  2335.00     3040.00         NaN       3052     1472.00      160.00   \n",
       "2   1954  5579.00     3088.00         NaN       3044     1656.00      266.00   \n",
       "3   1955  3256.00     2951.00         NaN       2841     1507.00     1151.00   \n",
       "4   1956  4170.00     2672.00         NaN       4060     1763.00      385.00   \n",
       "..   ...      ...         ...         ...        ...         ...         ...   \n",
       "63  2015  1241.93      648.59         NaN       7.54      412.81      430.63   \n",
       "64  2016  1227.68      691.36         NaN      54.13      777.81      508.31   \n",
       "65  2017  2221.07      913.09         NaN      49.37      605.47      664.84   \n",
       "66  2018   644.88      699.71         NaN       9.26      370.62      431.18   \n",
       "67  2019   941.18     1002.17         3.0          +      718.07      371.86   \n",
       "\n",
       "    Unnamed: 7    KOR Unnamed: 9  ...  Unnamed: 20 Unnamed: 21   USA  \\\n",
       "0      7680.00    NaN        NaN  ...          NaN         NaN   NaN   \n",
       "1      5570.00    NaN        NaN  ...          NaN         NaN   NaN   \n",
       "2      5366.00    NaN        NaN  ...          NaN         NaN   NaN   \n",
       "3     14016.00    NaN        NaN  ...          NaN         NaN   NaN   \n",
       "4     20979.00    NaN        NaN  ...          NaN         NaN   NaN   \n",
       "..         ...    ...        ...  ...          ...         ...   ...   \n",
       "63     3644.55   1.00        NaN  ...            -           -   4.4   \n",
       "64     5095.30   3.04        NaN  ...            -           -   9.2   \n",
       "65     4539.69   3.45        NaN  ...            +           -   1.1   \n",
       "66     4049.44   7.08        NaN  ...            +           -  18.3   \n",
       "67     4464.47  35.75        NaN  ...            +           -   9.6   \n",
       "\n",
       "    Unnamed: 23 Unnamed: 24 Unnamed: 25 Unnamed: 26 Unnamed: 27 Unnamed: 28  \\\n",
       "0           NaN         NaN         NaN         NaN         NaN      2076.0   \n",
       "1           NaN         NaN         NaN         NaN         NaN      4433.0   \n",
       "2           NaN         NaN         NaN         NaN         NaN      9537.0   \n",
       "3           NaN         NaN         NaN         NaN         NaN      6173.0   \n",
       "4           NaN         NaN         NaN         NaN         NaN      5727.0   \n",
       "..          ...         ...         ...         ...         ...         ...   \n",
       "63          0.2         NaN         NaN         7.2         0.2        86.4   \n",
       "64          0.6         NaN         NaN        30.6         0.3       315.7   \n",
       "65          1.4         NaN         NaN        18.4         NaN       466.4   \n",
       "66          0.5         NaN         0.1        31.0         4.4        11.5   \n",
       "67          1.7         NaN         0.5        36.1         1.3       226.1   \n",
       "\n",
       "   Unnamed: 29  \n",
       "0          2.0  \n",
       "1         48.0  \n",
       "2         11.0  \n",
       "3         93.0  \n",
       "4        388.0  \n",
       "..         ...  \n",
       "63       400.3  \n",
       "64       371.8  \n",
       "65       450.6  \n",
       "66       504.6  \n",
       "67       428.5  \n",
       "\n",
       "[68 rows x 30 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Open the saved XLS file with the catch data first\n",
    "df = pd.read_excel(\"ISC20_Catchtable_2020_5.xls\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "c0a0d6f1",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1952</td>\n",
       "      <td>JPN</td>\n",
       "      <td>2145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1953</td>\n",
       "      <td>JPN</td>\n",
       "      <td>2335.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1954</td>\n",
       "      <td>JPN</td>\n",
       "      <td>5579.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1955</td>\n",
       "      <td>JPN</td>\n",
       "      <td>3256.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1956</td>\n",
       "      <td>JPN</td>\n",
       "      <td>4170.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1967</th>\n",
       "      <td>2015</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>400.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968</th>\n",
       "      <td>2016</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>371.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1969</th>\n",
       "      <td>2017</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>450.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970</th>\n",
       "      <td>2018</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>504.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1971</th>\n",
       "      <td>2019</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>428.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1972 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year     variable   value\n",
       "0     1952          JPN  2145.0\n",
       "1     1953          JPN  2335.0\n",
       "2     1954          JPN  5579.0\n",
       "3     1955          JPN  3256.0\n",
       "4     1956          JPN  4170.0\n",
       "...    ...          ...     ...\n",
       "1967  2015  Unnamed: 29   400.3\n",
       "1968  2016  Unnamed: 29   371.8\n",
       "1969  2017  Unnamed: 29   450.6\n",
       "1970  2018  Unnamed: 29   504.6\n",
       "1971  2019  Unnamed: 29   428.5\n",
       "\n",
       "[1972 rows x 3 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pivot the table\n",
    "df_pivot = pd.melt(df,id_vars=['Year'])\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "546488ca",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Country</th>\n",
       "      <th>Catch Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1952</td>\n",
       "      <td>JPN</td>\n",
       "      <td>2145.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1953</td>\n",
       "      <td>JPN</td>\n",
       "      <td>2335.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1954</td>\n",
       "      <td>JPN</td>\n",
       "      <td>5579.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1955</td>\n",
       "      <td>JPN</td>\n",
       "      <td>3256.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1956</td>\n",
       "      <td>JPN</td>\n",
       "      <td>4170.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1967</th>\n",
       "      <td>2015</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>400.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1968</th>\n",
       "      <td>2016</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>371.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1969</th>\n",
       "      <td>2017</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>450.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1970</th>\n",
       "      <td>2018</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>504.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1971</th>\n",
       "      <td>2019</td>\n",
       "      <td>Unnamed: 29</td>\n",
       "      <td>428.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1972 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year      Country Catch Volume\n",
       "0     1952          JPN       2145.0\n",
       "1     1953          JPN       2335.0\n",
       "2     1954          JPN       5579.0\n",
       "3     1955          JPN       3256.0\n",
       "4     1956          JPN       4170.0\n",
       "...    ...          ...          ...\n",
       "1967  2015  Unnamed: 29        400.3\n",
       "1968  2016  Unnamed: 29        371.8\n",
       "1969  2017  Unnamed: 29        450.6\n",
       "1970  2018  Unnamed: 29        504.6\n",
       "1971  2019  Unnamed: 29        428.5\n",
       "\n",
       "[1972 rows x 3 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#rename the columns to appropriate names\n",
    "df_pivot.columns=['Year','Country','Catch Volume']\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "eeb35f4e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Year</th>\n",
       "      <th>Country</th>\n",
       "      <th>Catch Volume</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1968</td>\n",
       "      <td>JPN</td>\n",
       "      <td>3058.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1969</td>\n",
       "      <td>JPN</td>\n",
       "      <td>2187.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1970</td>\n",
       "      <td>JPN</td>\n",
       "      <td>1779.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1971</td>\n",
       "      <td>JPN</td>\n",
       "      <td>1555.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1972</td>\n",
       "      <td>JPN</td>\n",
       "      <td>1107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1898</th>\n",
       "      <td>2014</td>\n",
       "      <td>USA.7</td>\n",
       "      <td>828.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1899</th>\n",
       "      <td>2015</td>\n",
       "      <td>USA.7</td>\n",
       "      <td>498.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1900</th>\n",
       "      <td>2016</td>\n",
       "      <td>USA.7</td>\n",
       "      <td>728.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1901</th>\n",
       "      <td>2017</td>\n",
       "      <td>USA.7</td>\n",
       "      <td>937.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1902</th>\n",
       "      <td>2018</td>\n",
       "      <td>USA.7</td>\n",
       "      <td>570.4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1428 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      Year Country  Catch Volume\n",
       "16    1968     JPN        3058.0\n",
       "17    1969     JPN        2187.0\n",
       "18    1970     JPN        1779.0\n",
       "19    1971     JPN        1555.0\n",
       "20    1972     JPN        1107.0\n",
       "...    ...     ...           ...\n",
       "1898  2014   USA.7         828.2\n",
       "1899  2015   USA.7         498.7\n",
       "1900  2016   USA.7         728.2\n",
       "1901  2017   USA.7         937.9\n",
       "1902  2018   USA.7         570.4\n",
       "\n",
       "[1428 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#filter the data so that only years 1968-2018 remain\n",
    "df_pivot= df_pivot[(df_pivot['Year']>1967) & (df_pivot['Year']<2019)]\n",
    "df_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "172f9943",
   "metadata": {},
   "outputs": [],
   "source": [
    "#export cleaned data as CSV file\n",
    "df_pivot.to_csv('Catch_Pivot_Data_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "9063ba8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "#now upload the CSV file of the cleaned biomass data\n",
    "biomass=pd.read_csv(\"Biomass_2020_2.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "4612145d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fishing Year</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1952</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>134751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1953</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>136428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1954</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>146741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1955</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>156398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1956</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>175824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>2014</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>2015</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>21014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>2016</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>25009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>2017</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>25632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>2018</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>28228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Fishing Year                    variable   value\n",
       "0            1952           Total Biomass (t)  134751\n",
       "1            1953           Total Biomass (t)  136428\n",
       "2            1954           Total Biomass (t)  146741\n",
       "3            1955           Total Biomass (t)  156398\n",
       "4            1956           Total Biomass (t)  175824\n",
       "..            ...                         ...     ...\n",
       "129          2014  Spawning Stock Biomass (t)   18503\n",
       "130          2015  Spawning Stock Biomass (t)   21014\n",
       "131          2016  Spawning Stock Biomass (t)   25009\n",
       "132          2017  Spawning Stock Biomass (t)   25632\n",
       "133          2018  Spawning Stock Biomass (t)   28228\n",
       "\n",
       "[134 rows x 3 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pivot the biomass data into tidy data\n",
    "biomass_pivot = pd.melt(biomass, id_vars=['Fishing Year'])\n",
    "biomass_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f5524269",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fishing Year</th>\n",
       "      <th>Biomass Measure</th>\n",
       "      <th>Biomass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1952</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>134751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1953</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>136428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1954</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>146741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1955</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>156398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1956</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>175824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>2014</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>2015</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>21014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>2016</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>25009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>2017</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>25632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>2018</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>28228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Fishing Year             Biomass Measure  Biomass\n",
       "0            1952           Total Biomass (t)   134751\n",
       "1            1953           Total Biomass (t)   136428\n",
       "2            1954           Total Biomass (t)   146741\n",
       "3            1955           Total Biomass (t)   156398\n",
       "4            1956           Total Biomass (t)   175824\n",
       "..            ...                         ...      ...\n",
       "129          2014  Spawning Stock Biomass (t)    18503\n",
       "130          2015  Spawning Stock Biomass (t)    21014\n",
       "131          2016  Spawning Stock Biomass (t)    25009\n",
       "132          2017  Spawning Stock Biomass (t)    25632\n",
       "133          2018  Spawning Stock Biomass (t)    28228\n",
       "\n",
       "[134 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#rename the columns to appropriate names\n",
    "biomass_pivot.columns=['Fishing Year','Biomass Measure','Biomass']\n",
    "biomass_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "91bfff11",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Fishing Year</th>\n",
       "      <th>Biomass Measure</th>\n",
       "      <th>Biomass</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1968</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>91408</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1969</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>80523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1970</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>74222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1971</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>66114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1972</td>\n",
       "      <td>Total Biomass (t)</td>\n",
       "      <td>64114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>2014</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>18503</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>2015</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>21014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>2016</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>25009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>2017</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>25632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>2018</td>\n",
       "      <td>Spawning Stock Biomass (t)</td>\n",
       "      <td>28228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>102 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Fishing Year             Biomass Measure  Biomass\n",
       "16           1968           Total Biomass (t)    91408\n",
       "17           1969           Total Biomass (t)    80523\n",
       "18           1970           Total Biomass (t)    74222\n",
       "19           1971           Total Biomass (t)    66114\n",
       "20           1972           Total Biomass (t)    64114\n",
       "..            ...                         ...      ...\n",
       "129          2014  Spawning Stock Biomass (t)    18503\n",
       "130          2015  Spawning Stock Biomass (t)    21014\n",
       "131          2016  Spawning Stock Biomass (t)    25009\n",
       "132          2017  Spawning Stock Biomass (t)    25632\n",
       "133          2018  Spawning Stock Biomass (t)    28228\n",
       "\n",
       "[102 rows x 3 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#filter the data so only years from 1968-2018 remain\n",
    "biomass_pivot = biomass_pivot[(biomass_pivot['Fishing Year']>1967) & (biomass_pivot['Fishing Year']<2019)]\n",
    "biomass_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "375ee068",
   "metadata": {},
   "outputs": [],
   "source": [
    "#export cleaned data as CSV file\n",
    "biomass_pivot.to_csv('Biomass_pivot_1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f3b79f28",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
